﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CommonLibrary.DataModel.BBS;
using System.Data.SqlClient;
using CommonLibrary.DB;
using System.Data;

namespace ServiceApplication.Dao.BBS
{
    public class BBSDao
    {
        /// <summary>
        ///  获得前15热门帖子
        /// </summary>
        /// <returns></returns>
        public InitCardEntities QueryAllHotCard()
        {
            InitCardEntities inCardEnts = null;
            String sql = String.Empty;
            sql += "SELECT TOP 15 [card_id],[card_title],[init_time],[reply_count],[user_name],c.[user_id] " +
                   "FROM bbs_card c,jc_user u WHERE u.[user_id]=c.[user_id] AND [card_state]='HOT' ";
            sql += "ORDER BY [reply_count] desc,[update_time] desc";

            var initCards = DBExecuteUtil.querySqlTable(sql);
            if (initCards == null || initCards.Rows.Count == 0)
                return null;

            Int32 rowsCount = initCards.Rows.Count;
            inCardEnts = new InitCardEntities();
            for (int rowIndex = 0; rowIndex < rowsCount; rowIndex++)
            {
                inCardEnts.Add(this.packInitCard(initCards.Rows[rowIndex]));
            }
            return inCardEnts;
        }

        /// <summary>
        ///  获得所有帖子
        /// </summary>
        /// <returns></returns>
        public InitCardEntities QueryAllInitCard()
        {
            InitCardEntities inCardEnts = null;
            String sql = String.Empty;
            sql += "SELECT [card_id],[card_title],[init_time],[reply_count],[user_name],c.[user_id] " +
                   "FROM bbs_card c,jc_user u WHERE c.[user_id]=u.[user_id] ";
            sql += "ORDER BY [reply_count] desc,[update_time] desc";
            var initCards = DBExecuteUtil.querySqlTable(sql);
            if (initCards == null || initCards.Rows.Count == 0)
                return null;

            Int32 rowsCount = initCards.Rows.Count;
            inCardEnts = new InitCardEntities();
            for (int rowIndex = 0; rowIndex < rowsCount; rowIndex++)
            {
                inCardEnts.Add(this.packInitCard(initCards.Rows[rowIndex]));
            }
            return inCardEnts;
        }

        /// <summary>
        ///  获得所有个人帖子
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public InitCardEntities QueryMyCards(String userId)
        {
            InitCardEntities inCardEnts = null;
            String sql = String.Empty;
            sql += "SELECT [card_id],[card_title],[init_time],[reply_count],[user_name],c.[user_id] " +
                "FROM bbs_card c,jc_user u WHERE c.[user_id]=u.[user_id] AND [user_id]=@userId ";
            sql += "ORDER BY [reply_count] desc,[update_time] desc";
            List<SqlParameter> @params = new List<SqlParameter>();
            @params.Add(new SqlParameter("@userId", userId));

            var initCards = DBExecuteUtil.querySqlTable(sql, @params.ToArray());
            if (initCards == null || initCards.Rows.Count == 0)
                return null;
            Int32 rowsCount = initCards.Rows.Count;
            inCardEnts = new InitCardEntities();
            for (int rowIndex = 0; rowIndex < rowsCount; rowIndex++)
            {
                inCardEnts.Add(this.packInitCard(initCards.Rows[rowIndex]));
            }
            return inCardEnts;
        }

        /// <summary>
        ///  获得帖子及所有回复
        /// </summary>
        /// <param name="inCarId"></param>
        /// <returns></returns>
        public InitCardEntity QueryInitCard(String inCarId)
        {
            InitCardEntity initCardEnty = null;
            String sql = "SELECT [card_id],[card_title],[update_time],[card_text],[reply_count],[user_name],c.[user_id],[init_time],[reply_count]" +
                " FROM bbs_card c,jc_user u WHERE c.[user_id]=u.[user_id] AND [card_id]=@cardId";

            List<SqlParameter> @params = new List<SqlParameter>();
            @params.Add(new SqlParameter("@cardId", inCarId));

            var initCard = DBExecuteUtil.querySqlTable(sql, @params.ToArray());
            if (initCard == null || initCard.Rows.Count == 0)
                return null;

            initCardEnty = this.packInitCard(initCard.Rows[0]);
            initCardEnty.ReplyCards = this.QueryAllReplyCard(inCarId);
            return initCardEnty;
        }

        /// <summary>
        ///  获得所有回复帖子
        /// </summary>
        /// <param name="inCarId"></param>
        /// <returns></returns>
        public ReplyCardEntities QueryAllReplyCard(String inCarId)
        {
            ReplyCardEntities reCardEnts = null;
            String sql = "SELECT [reply_id],[card_id],[reply_time],[reply_text],[user_name],c.[user_id]" +
                " FROM bbs_reply_card c,jc_user u WHERE c.[user_id]=u.[user_id] AND [card_id]=@cardId";

            List<SqlParameter> @params = new List<SqlParameter>();
            @params.Add(new SqlParameter("@cardId", inCarId));

            var replyCards = DBExecuteUtil.querySqlTable(sql, @params.ToArray());
            if (replyCards == null || replyCards.Rows.Count == 0)
                return null;

            Int32 rowsCount = replyCards.Rows.Count;
            reCardEnts = new ReplyCardEntities();
            for (int rowIndex = 0; rowIndex < rowsCount; rowIndex++)
            {
                reCardEnts.Add(this.packReplyCard(replyCards.Rows[rowIndex], (rowIndex + 1)));
            }
            return reCardEnts;
        }

        /// <summary>
        ///  发帖
        /// </summary>
        /// <param name="initEnty"></param>
        /// <returns></returns>
        public String AddInitCard(InitCardEntity initEnty)
        {
            String sql = "INSERT INTO bbs_card ([user_id],[card_title],[card_text],[init_time],[update_time],[card_state],[reply_count]) " +
                "values (@userId,@title,@text,@initTime,@initTime,@state,@replyCount)";

            List<SqlParameter> @params = new List<SqlParameter>();
            @params.Add(new SqlParameter("@userId", initEnty.User.user_id));
            @params.Add(new SqlParameter("@title", initEnty.CardTitle));
            @params.Add(new SqlParameter("@text", initEnty.CardText));
            @params.Add(new SqlParameter("@initTime", initEnty.InitTime));
            @params.Add(new SqlParameter("@state", initEnty.CardState));
            @params.Add(new SqlParameter("@replyCount", initEnty.ReplyCount));

            String result = DBExecuteUtil.getUniqueResult(sql, @params.ToArray());
            return result;
        }

        /// <summary>
        ///  添加回复
        /// </summary>
        /// <param name="replyEnty"></param>
        /// <param name="inCarId"></param>
        /// <returns></returns>
        public void AddReplyCard(ReplyCardEntity replyEnty)
        {
            String[] sqlAry = new String[2];
            sqlAry[0] = "INSERT INTO bbs_reply_card ([card_id],[user_id],[reply_time],[reply_text]) VALUES " +
                " (@cardId,@userId,@replyTime,@replyText)";

            sqlAry[1] = "UPDATE bbs_card SET [update_time]=@updateTime,[reply_count]=[reply_count]+1 WHERE card_id=@cardId";

            List<SqlParameter[]> @params = new List<SqlParameter[]>();

            @params.Add(new SqlParameter[]{
                new SqlParameter("@cardId",replyEnty.InitCardId),
                new SqlParameter("@userId", replyEnty.User.user_id),
                new SqlParameter("@replyTime", replyEnty.ReplyTime),
                new SqlParameter("@replyText", replyEnty.CardText)
            });

            @params.Add(new SqlParameter[]{
                new SqlParameter("@cardId",replyEnty.InitCardId),
                new SqlParameter("@updateTime", replyEnty.ReplyTime)
            });

            //@params.Add(new SqlParameter("@cardId", inCarId));
            //@params.Add(new SqlParameter("@userId", replyEnty.User.id));
            //@params.Add(new SqlParameter("@replyTime", replyEnty.ReplyTime));
            //@params.Add(new SqlParameter("@replyText", replyEnty.CardText));
            //@params.Add(new SqlParameter("@updateTime", DateTime.Now.ToString("yyyy-MM-dd")));

            //String result = DBTranUtil.executeSqlArrayWithParas(sqlAry, @params);
            DBTranUtil.executeSqlArrayWithParas(sqlAry, @params);

        }

        /// <summary>
        ///  删除帖子
        /// </summary>
        /// <param name="inCardId"></param>
        /// <returns></returns>
        public void DelInitCard(String inCardId)
        {
            String[] sqlAry = new String[2];
            sqlAry[0] = "DELETE bbs_reply_card WHERE card_id=@cardId";
            sqlAry[1] = "DELETE bbs_card WHERE card_id=@cardId";

            List<SqlParameter[]> @params = new List<SqlParameter[]>();
            @params.Add(new SqlParameter[] { new SqlParameter("@cardId", inCardId) });
            @params.Add(new SqlParameter[] { new SqlParameter("@cardId", inCardId) });

            DBTranUtil.executeSqlArrayWithParas(sqlAry, @params);
        }

        /// <summary>
        ///  删除回复帖子
        /// </summary>
        /// <param name="reCardId"></param>
        /// <returns></returns>
        public String DelReplyCard(String reCardId)
        {
            String sql = "DELETE bbs_reply_card WHERE reply_id=@replyId";

            List<SqlParameter> @params = new List<SqlParameter>();
            @params.Add(new SqlParameter("@replyId", reCardId));

            String result = DBExecuteUtil.getUniqueResult(sql, @params.ToArray());
            return result;
        }

        /// <summary>
        ///  标记热门帖子
        /// </summary>
        /// <param name="inCardId"></param>
        /// <returns></returns>
        public String MarkHotCard(String inCardId, String updateTime)
        {
            String sql = "UPDATE bbs_card SET [card_state]='HOT',[update_time]=@update WHERE [card_id]=@cardId";


            List<SqlParameter> @params = new List<SqlParameter>();
            @params.Add(new SqlParameter("@cardId", inCardId));
            @params.Add(new SqlParameter("@update", updateTime));

            String result = DBExecuteUtil.getUniqueResult(sql, @params.ToArray());
            return result;
        }

        /// <summary>
        ///  封装帖子实体
        /// </summary>
        /// <param name="inirRow"></param>
        /// <returns></returns>
        private InitCardEntity packInitCard(DataRow inirRow)
        {
            InitCardEntity initCardEnty = new InitCardEntity();
            initCardEnty.User = new CommonLibrary.DataModel.Basic.UserEntity();
            foreach (DataColumn colum in inirRow.Table.Columns)
            {
                switch (colum.ColumnName)
                {
                    case "card_id":
                        initCardEnty.CardId = inirRow["card_id"] == null ? null : inirRow["card_id"].ToString();
                        break;

                    case "card_state":
                        initCardEnty.CardState = inirRow["card_state"] == null ? null : inirRow["card_state"].ToString();
                        break;

                    case "card_text":

                        initCardEnty.CardText = inirRow["card_text"] == null ? null : inirRow["card_text"].ToString();
                        break;

                    case "card_title":
                        initCardEnty.CardTitle = inirRow["card_title"] == null ? null : inirRow["card_title"].ToString();
                        break;

                    case "reply_count":
                        initCardEnty.ReplyCount = inirRow["reply_count"] == null ? null : inirRow["reply_count"].ToString();
                        break;

                    case "init_time":
                        initCardEnty.InitTime = inirRow["init_time"] == null ? null : inirRow["init_time"].ToString();
                        break;

                    case "user_id":
                        initCardEnty.User.user_id = inirRow["user_id"] == null ? 0 : (Int32)inirRow["user_id"];
                        break;

                    case "name":
                        initCardEnty.User.user_name = inirRow["name"] == null ? null : inirRow["name"].ToString();
                        break;
                }
            }
            return initCardEnty;
        }

        /// <summary>
        ///  封装回复实体
        /// </summary>
        /// <param name="replyRow"></param>
        /// <returns></returns>
        private ReplyCardEntity packReplyCard(DataRow replyRow, Int32 buildCount)
        {
            ReplyCardEntity replyEnty = new ReplyCardEntity();
            replyEnty.User = new CommonLibrary.DataModel.Basic.UserEntity();

            foreach (DataColumn colum in replyRow.Table.Columns)
            {
                switch (colum.ColumnName)
                {
                    //case "card_id":
                    //    replyEnty.CardId = replyRow["card_id"] == null ? null : replyRow["card_id"].ToString();
                    //    break;
                    case "reply_id":
                        replyEnty.CardId = replyRow["reply_id"] == null ? null : replyRow["reply_id"].ToString();
                        break;

                    case "reply_text":
                        replyEnty.CardText = replyRow["reply_text"] == null ? null : replyRow["reply_text"].ToString();
                        break;

                    case "reply_time":
                        replyEnty.ReplyTime = replyRow["reply_time"] == null ? null : replyRow["reply_time"].ToString();
                        break;

                    case "user_id":
                        replyEnty.User.user_id = replyRow["user_id"] == null ? 0 : (Int32)replyRow["user_id"];
                        break;

                    case "name":
                        replyEnty.User.user_name = replyRow["name"] == null ? null : replyRow["name"].ToString();
                        break;
                }
            }
            replyEnty.BuildCount = buildCount;
            return replyEnty;
        }
    }
}
